﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// Summary description for DA_Invoice
/// </summary>
public class DA_Invoice
{
    AuditBillingEntities objABEnt;
    public DA_Invoice()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public List<PLInvoice> DA_fnGetInvoiceTransactions(PLInvoice pObjInvoice)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            List<int> lstArr;
            List<string> lstArrStr;
            var varRes = (from inv in objABEnt.TrnInvoices
                          join cnt in objABEnt.TrnContracts on inv.TrnContractID equals cnt.TrnContractID
                          join c in objABEnt.MstContracts on cnt.MstContractID equals c.MstContractID
                          join com in objABEnt.MstCompanies on inv.MstCompanyID equals com.MstCompanyID
                          join cli in objABEnt.MstClients on cnt.MstClientID equals cli.MstClientID
                          join pay in objABEnt.MstPaymentModes on cnt.MstPaymentModeID equals pay.MstPaymentModeID
                          join payl in objABEnt.MstPaymentLevels on inv.MstPaymentLevelID equals payl.MstPaymentLevelID
                          where (cnt.FinancialYearID ?? 0) == 0
                          orderby inv.InvoiceNo
                          select new PLInvoice()
                          {

                              TrnInvoiceID = inv.TrnInvoiceID,
                              InvoiceNo = inv.InvoiceNo,
                              InvoiceDate = inv.InvoiceDate,
                              PaymentLevelPer = inv.PaymentLevelPer,
                              GrossInvoiceAmount = inv.GrossInvoiceAmount,
                              ServiceTaxPer = inv.ServiceTaxPer,
                              ServiceTaxAmount = inv.ServiceTaxAmount,
                              FinalInvoiceAmount = inv.FinalInvoiceAmount,
                              IsActive = inv.IsActive,
                              IsCompleted = inv.IsCompleted,
                              AllowForDelete = (objABEnt.TrnCollections.Where(s => s.TrnInvoiceID == inv.TrnInvoiceID).Count() > 0 ? false : true),

                              CompanyID = cnt.MstCompanyID,
                              ClientID = cnt.MstClientID,
                              ContractID = cnt.MstContractID,
                              PaymentModeID = cnt.MstPaymentModeID,
                              TrnContractID = cnt.TrnContractID,
                              PaymentLevelID = inv.MstPaymentLevelID,
                              PaymentLevelName = payl.Name,

                              CompanyName = com.Name,
                              ContractName = c.Name,
                              ClientName = cli.ClientName,
                              PaymentModeName = pay.PaymentModeName,
                              ContractNo = cnt.ContractNo
                          });

            if (!string.IsNullOrEmpty(pObjInvoice.SeachClientIDs))
            {
                lstArr = pObjInvoice.SeachClientIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ClientID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchCompanyIDs))
            {
                lstArr = pObjInvoice.SearchCompanyIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.CompanyID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchContractIDs))
            {
                lstArr = pObjInvoice.SearchContractIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ContractID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchPaymentModeIDs))
            {
                lstArr = pObjInvoice.SearchPaymentModeIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.PaymentModeID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SeachContractNo))
            {
                lstArrStr = pObjInvoice.SeachContractNo.Split('~').ToList();
                varRes = varRes.Where(s => lstArrStr.Contains(s.ContractNo));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.InvoiceNo))
            {
                lstArrStr = pObjInvoice.InvoiceNo.Split('~').ToList();
                varRes = varRes.Where(s => lstArrStr.Contains(s.InvoiceNo));
            }

            if (pObjInvoice.SearchIsActive != null)
                varRes = varRes.Where(s => s.IsActive == pObjInvoice.SearchIsActive);
            if (pObjInvoice.IsCompleted != null)
                varRes = varRes.Where(s => s.IsCompleted == pObjInvoice.IsCompleted);

            if (pObjInvoice.SearchInvoiceStartDate != null)
                varRes = varRes.Where(s => s.InvoiceDate >= pObjInvoice.SearchInvoiceStartDate);
            if (pObjInvoice.SearchInvoiceEndDate != null)
                varRes = varRes.Where(s => s.InvoiceDate <= pObjInvoice.SearchInvoiceEndDate);

            if (pObjInvoice.ContractID.GetValueOrDefault() != 0)
            {
                varRes = varRes.Where(s => s.TrnContractID == pObjInvoice.ContractID);
            }

            return varRes.ToList();
        }
    }

    public PLInvoice DA_fnGetSingleInvoiceTransactions(PLInvoice pObjInvoice)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from inv in objABEnt.TrnInvoices
                          join cnt in objABEnt.TrnContracts on inv.TrnContractID equals cnt.TrnContractID
                          where inv.TrnInvoiceID == pObjInvoice.TrnInvoiceID
                          select new PLInvoice()
                          {

                              CompanyID = cnt.MstCompanyID,
                              ClientID = cnt.MstClientID,
                              ContractID = cnt.MstContractID,
                              PaymentModeID = cnt.MstPaymentModeID,
                              TrnContractID = cnt.TrnContractID,
                              TrnInvoiceID = inv.TrnInvoiceID
                          }).SingleOrDefault();

            return varRes;
        }
    }

    public int DA_InsertUpdateContractTransactions(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            TrnContract objContract = new TrnContract();
            if (pObjContract.TrnContractID != 0)
            {
                objContract = (from u in objABEnt.TrnContracts
                               where u.TrnContractID == pObjContract.TrnContractID
                               select u).ToList().FirstOrDefault();
            }

            objContract.MstCompanyID = pObjContract.CompanyID.GetValueOrDefault();
            objContract.MstClientID = pObjContract.ClientID.GetValueOrDefault();
            objContract.MstContractID = pObjContract.ContractID.GetValueOrDefault();
            objContract.MstPaymentModeID = pObjContract.PaymentModeID.GetValueOrDefault();

            objContract.ContractStartDate = pObjContract.ContractStartDate.GetValueOrDefault();
            objContract.ContractAmount = pObjContract.ContractAmount.GetValueOrDefault();
            objContract.IsActive = pObjContract.IsActive.GetValueOrDefault();


            if (pObjContract.TrnContractID != 0)
            {
                objContract.ModifiedBy = SessionInfo.prop_getObj.EmployeeID;
                objContract.ModifiedOn = DateTime.Now;
            }
            else
            {
                var varFirstRec = (from u in objABEnt.TrnContracts
                                   orderby u.TrnContractID descending
                                   select u.ContractNo).FirstOrDefault();
                if (varFirstRec == null)
                    objContract.ContractNo = (1).ToString("00000");
                else
                    objContract.ContractNo = (TrynParse.parseInt(varFirstRec) + 1).ToString("00000");

                objContract.CreatedBy = SessionInfo.prop_getObj.EmployeeID;
                objContract.CreatedOn = DateTime.Now;
                objABEnt.TrnContracts.Add(objContract);
            }
            objABEnt.SaveChanges();
            return objContract.MstContractID;
        }
    }

    public List<PLClient> DA_GetPendingClients(PLContract pObjContract)
    {
        var varPendingList = DA_fnGetInvoiceTransactions(pObjContract);
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from l in varPendingList
                          join c in objABEnt.MstClients on l.ClientID equals c.MstClientID
                          group c by new { c.MstClientID, c.ClientName } into grp
                          select new PLClient()
                          {
                              ClientID = grp.Key.MstClientID,
                              ClientName = grp.Key.ClientName
                          }).ToList();
            return varRes;
        }
    }

    public List<PLContract> DA_GetPendingContracts(PLContract pObjContract)
    {
        var varPendingList = DA_fnGetInvoiceTransactions(pObjContract);
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from l in varPendingList
                          join c in objABEnt.MstContracts on l.ContractID equals c.MstContractID
                          group c by new { c.MstContractID, c.Name } into grp
                          select new PLContract()
                          {
                              ContractID = grp.Key.MstContractID,
                              ContractName = grp.Key.Name
                          }).ToList();
            return varRes;
        }
    }

    public List<PLContract> DA_GetTrnContract(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from c in objABEnt.TrnContracts
                          where (pObjContract.SearchIsActive == null ? true : (c.IsActive == pObjContract.SearchIsActive || c.IsCompleted == pObjContract.SearchIsActive)) &&
                                c.MstCompanyID == pObjContract.CompanyID &&
                                c.MstClientID == pObjContract.ClientID &&
                                c.MstContractID == pObjContract.ContractID &&
                                c.MstPaymentModeID == pObjContract.PaymentModeID
                          select new PLContract()
                          {
                              TrnContractID = c.TrnContractID,
                              ContractNo = c.ContractNo
                          }).ToList();
            return varRes;
        }
    }

    public List<PLContract> DA_GetPendingTrnContracts(PLContract pObjContract)
    {
        var varPendingList = DA_fnGetInvoiceTransactions(pObjContract);
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from l in varPendingList
                          join c in objABEnt.TrnContracts on l.TrnContractID equals c.TrnContractID
                          group c by new { c.TrnContractID, c.ContractNo } into grp
                          select new PLContract()
                          {
                              TrnContractID = grp.Key.TrnContractID,
                              ContractName = grp.Key.ContractNo
                          }).ToList();
            return varRes;
        }
    }

    public PLContract DA_GetPendingTrnContractsDetails(PLContract pObjContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from t in objABEnt.TrnContracts
                          join com in objABEnt.MstCompanies on t.MstCompanyID equals com.MstCompanyID
                          join cl in objABEnt.MstClients on t.MstClientID equals cl.MstClientID
                          join pay in objABEnt.MstPaymentModes on t.MstPaymentModeID equals pay.MstPaymentModeID
                          where t.TrnContractID == pObjContract.TrnContractID
                          select new PLContract()
                          {
                              TrnContractID = t.TrnContractID,
                              CompanyID = t.MstCompanyID,
                              ClientID = t.MstClientID,
                              ContractID = t.MstContractID,
                              ContractNo = t.ContractNo,
                              PaymentModeID = t.MstPaymentModeID,
                              PaymentModeName = pay.PaymentModeName,
                              ContractAmount = t.ContractAmount
                          }).FirstOrDefault();
            var varPaymentLevel = (from i in objABEnt.TrnInvoices
                                   join level in objABEnt.MstPaymentLevels on i.MstPaymentLevelID equals level.MstPaymentLevelID
                                   where i.TrnContractID == pObjContract.TrnContractID
                                   select new PLInvoice()
                                   {
                                       InvoiceDate = i.InvoiceDate,
                                       PaymentLevelPer = i.PaymentLevelPer,
                                       GrossInvoiceAmount = i.GrossInvoiceAmount,
                                       PaymentLevelSortIndex = level.LevelSort
                                   }).ToList();

            varRes.CompletedContractPer = varRes.InvoicedContractPer = varPaymentLevel.Sum(s => s.PaymentLevelPer.Value);
            varRes.CompletedInvoiceAmount = varPaymentLevel.Sum(s => s.GrossInvoiceAmount.Value);
            varRes.LastInvoiceDate = varPaymentLevel.OrderByDescending(s => s.InvoiceDate).Select(s => s.InvoiceDate).FirstOrDefault();

            int intMaxSortOrder = (varPaymentLevel.Count == 0 ? 0 : varPaymentLevel.Max(s => s.PaymentLevelSortIndex.Value));
            var varNextPaymentMode = objABEnt.MstPaymentLevels.Where(s => s.MstPaymentModeID == varRes.PaymentModeID && s.LevelSort > intMaxSortOrder).OrderBy(s => s.LevelSort).FirstOrDefault();
            var varLastPaymentMode = objABEnt.MstPaymentLevels.Where(s => s.MstPaymentModeID == varRes.PaymentModeID && s.LevelSort > varNextPaymentMode.LevelSort).OrderBy(s => s.LevelSort).FirstOrDefault();

            varRes.PaymentLevelID = varNextPaymentMode.MstPaymentLevelID;
            varRes.PaymentLevelName = varNextPaymentMode.Name;
            if (varLastPaymentMode == null)
            {
                varRes.IsLastPaymentMode = true;
                varRes.PaymentLevelPer = 100 - varRes.InvoicedContractPer;
            }
            else
            {
                varRes.PaymentLevelPer = varNextPaymentMode.Percentage;
            }
            return varRes;
        }
    }

    public PLContract DA_GetTrnContractsDetails_Edit(PLContract pObjContract)
    {
        using (AuditBillingEntities objABEnt = new AuditBillingEntities())
        {
            var varCurrInvoice = objABEnt.TrnInvoices.Where(s => s.TrnInvoiceID == pObjContract.InvoiceID).FirstOrDefault();
            var varRes = (from t in objABEnt.TrnContracts
                          join com in objABEnt.MstCompanies on t.MstCompanyID equals com.MstCompanyID
                          join cl in objABEnt.MstClients on t.MstClientID equals cl.MstClientID
                          join pay in objABEnt.MstPaymentModes on t.MstPaymentModeID equals pay.MstPaymentModeID
                          where t.TrnContractID == pObjContract.TrnContractID
                          select new PLContract()
                          {
                              TrnContractID = t.TrnContractID,
                              CompanyID = t.MstCompanyID,
                              ClientID = t.MstClientID,
                              ContractID = t.MstContractID,
                              ContractNo = t.ContractNo,
                              PaymentModeID = t.MstPaymentModeID,
                              PaymentModeName = pay.PaymentModeName,
                              ContractAmount = t.ContractAmount
                          }).FirstOrDefault();
            var varPaymentLevel = (from i in objABEnt.TrnInvoices
                                   join level in objABEnt.MstPaymentLevels on i.MstPaymentLevelID equals level.MstPaymentLevelID
                                   where i.TrnContractID == pObjContract.TrnContractID
                                   select new PLInvoice()
                                   {
                                       InvoiceDate = i.InvoiceDate,
                                       PaymentLevelPer = i.PaymentLevelPer,
                                       GrossInvoiceAmount = i.GrossInvoiceAmount,
                                       PaymentLevelSortIndex = level.LevelSort
                                   }).ToList();

            varRes.CompletedContractPer = varRes.InvoicedContractPer = varPaymentLevel.Sum(s => s.PaymentLevelPer.Value);
            varRes.CompletedInvoiceAmount = varPaymentLevel.Sum(s => s.GrossInvoiceAmount.Value);
            varRes.LastInvoiceDate = varPaymentLevel.OrderByDescending(s => s.InvoiceDate).Select(s => s.InvoiceDate).FirstOrDefault();

            var varCurrentPaymentMode = objABEnt.MstPaymentLevels.Where(s => s.MstPaymentLevelID == varCurrInvoice.MstPaymentLevelID).FirstOrDefault();
            int intMaxSortOrder = (varPaymentLevel.Count == 0 ? 0 : varPaymentLevel.Max(s => s.PaymentLevelSortIndex.Value));
            var varLastPaymentMode = objABEnt.MstPaymentLevels.Where(s => s.MstPaymentModeID == varRes.PaymentModeID && s.LevelSort > varCurrentPaymentMode.LevelSort).OrderBy(s => s.LevelSort).FirstOrDefault();

            varRes.PaymentLevelID = varCurrentPaymentMode.MstPaymentLevelID;
            varRes.PaymentLevelName = varCurrentPaymentMode.Name;
            varRes.PaymentLevelPer = varCurrentPaymentMode.Percentage;

            if (varLastPaymentMode == null)
            {
                varRes.IsLastPaymentMode = true;
            }
            return varRes;
        }
    }

    public PLContract DA_GetTrnContractsDetails_Editing(PLContract pObjContract)
    {
        using (AuditBillingEntities objABEnt = new AuditBillingEntities())
        {
            var varCurrInvoice = objABEnt.TrnInvoices.Where(s => s.TrnInvoiceID == pObjContract.InvoiceID).FirstOrDefault();
            var varRes = (from t in objABEnt.TrnContracts
                          join com in objABEnt.MstCompanies on t.MstCompanyID equals com.MstCompanyID
                          join cl in objABEnt.MstClients on t.MstClientID equals cl.MstClientID
                          join pay in objABEnt.MstPaymentModes on t.MstPaymentModeID equals pay.MstPaymentModeID
                          where t.TrnContractID == pObjContract.TrnContractID
                          select new PLContract()
                          {
                              TrnContractID = t.TrnContractID,
                              CompanyID = t.MstCompanyID,
                              ClientID = t.MstClientID,
                              ContractID = t.MstContractID,
                              ContractNo = t.ContractNo,
                              PaymentModeID = t.MstPaymentModeID,
                              PaymentModeName = pay.PaymentModeName,
                              ContractAmount = t.ContractAmount
                          }).FirstOrDefault();
            var varPaymentLevel = (from i in objABEnt.TrnInvoices
                                   join level in objABEnt.MstPaymentLevels on i.MstPaymentLevelID equals level.MstPaymentLevelID
                                   where i.TrnContractID == pObjContract.TrnContractID
                                   select new PLInvoice()
                                   {
                                       InvoiceDate = i.InvoiceDate,
                                       PaymentLevelPer = i.PaymentLevelPer,
                                       GrossInvoiceAmount = i.GrossInvoiceAmount,
                                       PaymentLevelSortIndex = level.LevelSort
                                   }).ToList();

            varRes.CompletedContractPer = varRes.InvoicedContractPer = varPaymentLevel.Sum(s => s.PaymentLevelPer.Value);
            varRes.CompletedInvoiceAmount = varPaymentLevel.Sum(s => s.GrossInvoiceAmount.Value);
            varRes.LastInvoiceDate = varPaymentLevel.OrderByDescending(s => s.InvoiceDate).Select(s => s.InvoiceDate).FirstOrDefault();

            var varCurrentPaymentMode = objABEnt.MstPaymentLevels.Where(s => s.MstPaymentLevelID == varCurrInvoice.MstPaymentLevelID).FirstOrDefault();
            int intMaxSortOrder = (varPaymentLevel.Count == 0 ? 0 : varPaymentLevel.Max(s => s.PaymentLevelSortIndex.Value));
            var varLastPaymentMode = objABEnt.MstPaymentLevels.Where(s => s.MstPaymentModeID == varRes.PaymentModeID && s.LevelSort > varCurrentPaymentMode.LevelSort).OrderBy(s => s.LevelSort).FirstOrDefault();

            varRes.PaymentLevelID = varCurrentPaymentMode.MstPaymentLevelID;
            varRes.PaymentLevelName = varCurrentPaymentMode.Name;
            varRes.PaymentLevelPer = varCurrentPaymentMode.Percentage;

            if (varLastPaymentMode == null)
            {
                varRes.IsLastPaymentMode = true;
            }
            return varRes;
        }
    }

    public List<PLContract> DA_fnGetInvoiceTransactions(PLContract pObjContract)
    {
        List<int> lstObjContractList = DA_GetPendingContractList(new PLContract());
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from u in objABEnt.TrnContracts
                          where lstObjContractList.Contains(u.TrnContractID)
                          select new PLContract()
                            {
                                TrnContractID = u.TrnContractID,
                                ContractNo = u.ContractNo,
                                CompanyID = u.MstCompanyID,
                                ClientID = u.MstClientID,
                                ContractID = u.MstContractID
                            });
            //if (pObjContract.CompanyID.GetValueOrDefault() > 0)
            //    varRes = varRes.Where(s => s.CompanyID == pObjContract.CompanyID);
            if (pObjContract.ClientID.GetValueOrDefault() > 0)
                varRes = varRes.Where(s => s.ClientID == pObjContract.ClientID);
            if (pObjContract.ContractID.GetValueOrDefault() > 0)
                varRes = varRes.Where(s => s.ContractID == pObjContract.ContractID);
            return varRes.ToList();
        }
    }

    public List<int> DA_GetPendingContractList(PLContract pContract)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from u in objABEnt.TrnContracts
                          join inv in objABEnt.TrnInvoices on u.TrnContractID equals inv.TrnContractID into LO_inv
                          from inv in LO_inv.DefaultIfEmpty()
                          where u.IsActive == true && u.IsCompleted == false
                          select new PLContract()
                          {
                              TrnContractID = u.TrnContractID,
                              ContractAmount = u.ContractAmount,
                              InvoiceAmount = (inv == null ? 0 : inv.GrossInvoiceAmount)
                          });
            varRes = (from u in varRes
                      group u by new { u.TrnContractID, u.ContractAmount } into newli
                      select new PLContract()
                        {
                            TrnContractID = newli.Key.TrnContractID,
                            ContractAmount = newli.Key.ContractAmount,
                            InvoiceAmount = newli.Sum(s => s.InvoiceAmount)
                        });

            List<int> lstContractIds = varRes.Where(s => s.ContractAmount > s.InvoiceAmount).Select(s => s.TrnContractID).Distinct().ToList();
            return lstContractIds;
        }
    }

    public int DA_InsertUpdateInvoiceTransactions(PLInvoice pObjInvoice)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            TrnInvoice objInvoice = new TrnInvoice();
            if (pObjInvoice.TrnInvoiceID == 0)
            {
                objInvoice = new TrnInvoice();

                TrnContract objCont = objABEnt.TrnContracts.Where(s => s.TrnContractID == pObjInvoice.TrnContractID).FirstOrDefault();

                string strlastInvoiceNo = (from i in objABEnt.TrnInvoices
                                           where i.TrnContractID == pObjInvoice.TrnContractID
                                           orderby i.TrnInvoiceID descending
                                           select i.InvoiceNo).FirstOrDefault();

                if (strlastInvoiceNo == null)
                    objInvoice.InvoiceNo = objCont.ContractNo + "/" + (1).ToString("000");
                else
                    objInvoice.InvoiceNo = objCont.ContractNo + "/" + (TrynParse.parseInt(strlastInvoiceNo.Replace(objCont.ContractNo + "/", "")) + 1).ToString("000");

                objInvoice.CreatedBy = SessionInfo.prop_getObj.EmployeeID;
                objInvoice.CreatedOn = DateTime.Now;
                objInvoice.IsCompleted = false;
            }
            else
            {
                objInvoice = (from i in objABEnt.TrnInvoices
                              where i.TrnInvoiceID == pObjInvoice.TrnInvoiceID
                              select i).FirstOrDefault();
                objInvoice.ModifiedBy = SessionInfo.prop_getObj.EmployeeID;
                objInvoice.ModifiedOn = DateTime.Now;
            }
            objInvoice.TrnInvoiceID = pObjInvoice.TrnInvoiceID;
            objInvoice.TrnContractID = pObjInvoice.TrnContractID;
            objInvoice.MstCompanyID = pObjInvoice.MstCompanyID;
            objInvoice.InvoiceDate = pObjInvoice.InvoiceDate;
            objInvoice.MstPaymentLevelID = pObjInvoice.MstPaymentLevelID;
            objInvoice.PaymentLevelPer = pObjInvoice.PaymentLevelPer;
            objInvoice.GrossInvoiceAmount = pObjInvoice.GrossInvoiceAmount;
            objInvoice.ServiceTaxPer = pObjInvoice.ServiceTaxPer;
            objInvoice.ServiceTaxAmount = pObjInvoice.ServiceTaxAmount;
            objInvoice.FinalInvoiceAmount = pObjInvoice.FinalInvoiceAmount;
            objInvoice.IsActive = pObjInvoice.IsActive;

            if (pObjInvoice.TrnInvoiceID == 0)
            {
                objInvoice.IsCompleted = false;
                objABEnt.TrnInvoices.Add(objInvoice);
            }
            if (pObjInvoice.ServiceTaxPer == 0)
            {
                objInvoice.MstServiceTaxID = null;
            }
            else
            {
                var varRes = (from i in objABEnt.MstServiceTaxes
                              where i.IsActive == true &&
                             pObjInvoice.InvoiceDate >= i.ServiceTaxEffStartDate && pObjInvoice.InvoiceDate <= i.ServiceTaxEffEndDate
                              select i).FirstOrDefault();
                if (varRes == null)
                    objInvoice.MstServiceTaxID = null;
                else
                    objInvoice.MstServiceTaxID = varRes.MstServiceTaxID;
            }
            objABEnt.SaveChanges();

            var objInvDet = (from cn in objABEnt.TrnContracts
                             join inv in objABEnt.TrnInvoices on cn.TrnContractID equals inv.TrnContractID
                             where cn.TrnContractID == pObjInvoice.TrnContractID
                             select new
                             {
                                 cn.ContractAmount,
                                 inv.GrossInvoiceAmount
                             });
            decimal decCntAmt = objInvDet.FirstOrDefault().ContractAmount.Value;
            decimal decInvlAmt = objInvDet.Sum(s => s.GrossInvoiceAmount).Value;
            if (decInvlAmt >= decCntAmt)
            {
                var varCnt = (from cn in objABEnt.TrnContracts
                              where cn.TrnContractID == pObjInvoice.TrnContractID
                              select cn).FirstOrDefault();
                varCnt.IsCompleted = true;
                objABEnt.SaveChanges();
            }


            return objInvoice.TrnInvoiceID;
        }
    }

    public PLInvoice DA_ContractFilePath(PLInvoice pObjInvoice)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from i in objABEnt.TrnInvoices
                          join c in objABEnt.TrnContracts on i.TrnContractID equals c.TrnContractID
                          join cl in objABEnt.MstClients on c.MstClientID equals cl.MstClientID
                          where i.TrnInvoiceID == pObjInvoice.TrnInvoiceID
                          select new PLInvoice()
                          {
                              TrnInvoiceID = i.TrnInvoiceID,
                              InvoiceNo = i.InvoiceNo,
                              ClientName = cl.ClientName,
                              ContractFilePath = c.ContractFilePath
                          }).FirstOrDefault();

            return varRes;
        }
    }

    public void DA_DeleteInvoice(PLInvoice pObjInvoice)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from i in objABEnt.TrnInvoices
                          where i.TrnInvoiceID == pObjInvoice.TrnInvoiceID
                          select i).FirstOrDefault();
            objABEnt.TrnInvoices.Remove(varRes);
            objABEnt.SaveChanges();
        }
    }

    public MstServiceTax DA_GetServiceTaxPer(PLInvoice pObjInvoice)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            var varRes = (from i in objABEnt.MstServiceTaxes
                          where i.IsActive == true &&
                         pObjInvoice.InvoiceDate >= i.ServiceTaxEffStartDate && pObjInvoice.InvoiceDate <= i.ServiceTaxEffEndDate
                          select i).FirstOrDefault();
            return varRes;
        }
    }

    public List<PLInvoice> DA_fnGetInvoiceReport(PLInvoice pObjInvoice)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            List<int> lstArr;
            List<string> lstArrStr;
            var varRes = (from inv in objABEnt.TrnInvoices
                          join cnt in objABEnt.TrnContracts on inv.TrnContractID equals cnt.TrnContractID
                          join c in objABEnt.MstContracts on cnt.MstContractID equals c.MstContractID
                          join com in objABEnt.MstCompanies on inv.MstCompanyID equals com.MstCompanyID
                          join cli in objABEnt.MstClients on cnt.MstClientID equals cli.MstClientID
                          join pay in objABEnt.MstPaymentModes on cnt.MstPaymentModeID equals pay.MstPaymentModeID
                          join payl in objABEnt.MstPaymentLevels on inv.MstPaymentLevelID equals payl.MstPaymentLevelID

                          join fin in objABEnt.FinancialYears on cnt.FinancialYearID equals fin.FinancialYearID into LO_fin
                          from fin in LO_fin.DefaultIfEmpty()
                          orderby inv.InvoiceNo
                          select new PLInvoice()
                          {
                              TrnInvoiceID = inv.TrnInvoiceID,
                              InvoiceNo = inv.InvoiceNo,
                              InvoiceDate = inv.InvoiceDate,
                              PaymentLevelPer = inv.PaymentLevelPer,
                              GrossInvoiceAmount = inv.GrossInvoiceAmount,
                              ServiceTaxPer = inv.ServiceTaxPer,
                              ServiceTaxAmount = inv.ServiceTaxAmount,
                              FinalInvoiceAmount = inv.FinalInvoiceAmount,
                              IsActive = inv.IsActive,

                              CompanyID = cnt.MstCompanyID,
                              ClientID = cnt.MstClientID,
                              ContractID = cnt.MstContractID,
                              PaymentModeID = cnt.MstPaymentModeID,
                              TrnContractID = cnt.TrnContractID,
                              PaymentLevelID = inv.MstPaymentLevelID,
                              PaymentLevelName = payl.Name,

                              CompanyName = com.Name,
                              ContractName = c.Name,
                              ClientName = cli.ClientName,
                              PaymentModeName = pay.PaymentModeName,
                              ContractNo = cnt.ContractNo,

                              FinancialYearID = cnt.FinancialYearID,
                              FinancialYearName = (fin == null ? "" : fin.FinYearName),
                              IsCompleted = inv.IsCompleted
                          });

            if (!string.IsNullOrEmpty(pObjInvoice.SeachClientIDs))
            {
                lstArr = pObjInvoice.SeachClientIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ClientID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchCompanyIDs))
            {
                lstArr = pObjInvoice.SearchCompanyIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.CompanyID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchContractIDs))
            {
                lstArr = pObjInvoice.SearchContractIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ContractID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchPaymentModeIDs))
            {
                lstArr = pObjInvoice.SearchPaymentModeIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.PaymentModeID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.FinancialYearIDs))
            {
                lstArr = pObjInvoice.FinancialYearIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.FinancialYearID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SeachContractNo))
            {
                lstArrStr = pObjInvoice.SeachContractNo.Split('~').ToList();
                varRes = varRes.Where(s => lstArrStr.Contains(s.ContractNo));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.InvoiceNo))
            {
                lstArrStr = pObjInvoice.InvoiceNo.Split('~').ToList();
                varRes = varRes.Where(s => lstArrStr.Contains(s.InvoiceNo));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.TrnContractIDs))
            {
                lstArr = pObjInvoice.TrnContractIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.TrnContractID));
            }
            
            if (pObjInvoice.SearchIsActive != null)
                varRes = varRes.Where(s => s.IsActive == pObjInvoice.SearchIsActive);

            if (pObjInvoice.IsCompleted != null)
                varRes = varRes.Where(s => (s.IsCompleted ?? false) == pObjInvoice.IsCompleted);

            if (pObjInvoice.SearchInvoiceStartDate != null)
                varRes = varRes.Where(s => s.InvoiceDate >= pObjInvoice.SearchInvoiceStartDate);
            if (pObjInvoice.SearchInvoiceEndDate != null)
                varRes = varRes.Where(s => s.InvoiceDate <= pObjInvoice.SearchInvoiceEndDate);

            if (pObjInvoice.ContractID.GetValueOrDefault() != 0)
            {
                varRes = varRes.Where(s => s.TrnContractID == pObjInvoice.ContractID);
            }

            return varRes.ToList();
        }
    }

    public List<PLInvoice> DA_fnGetReceivableReport(PLInvoice pObjInvoice)
    {
        using (objABEnt = new AuditBillingEntities())
        {
            List<int> lstArr;
            List<string> lstArrStr;
            var varRes = (from inv in objABEnt.TrnInvoices
                          join cnt in objABEnt.TrnContracts on inv.TrnContractID equals cnt.TrnContractID
                          join c in objABEnt.MstContracts on cnt.MstContractID equals c.MstContractID
                          join com in objABEnt.MstCompanies on inv.MstCompanyID equals com.MstCompanyID
                          join cli in objABEnt.MstClients on cnt.MstClientID equals cli.MstClientID
                          join pay in objABEnt.MstPaymentModes on cnt.MstPaymentModeID equals pay.MstPaymentModeID
                          join payl in objABEnt.MstPaymentLevels on inv.MstPaymentLevelID equals payl.MstPaymentLevelID
                          let InvdAmt = ((objABEnt.TrnInvoices.Where(s => s.TrnInvoiceID == inv.TrnInvoiceID).Sum(s => s.GrossInvoiceAmount)))
                          where inv.IsCompleted == false && inv.IsActive == true
                          orderby inv.InvoiceNo
                          select new PLInvoice()
                          {
                              TrnInvoiceID = inv.TrnInvoiceID,
                              InvoiceNo = inv.InvoiceNo,
                              InvoiceDate = inv.InvoiceDate,
                              PaymentLevelPer = inv.PaymentLevelPer,
                              GrossInvoiceAmount = inv.GrossInvoiceAmount,
                              ServiceTaxPer = inv.ServiceTaxPer,
                              ServiceTaxAmount = inv.ServiceTaxAmount,
                              FinalInvoiceAmount = inv.FinalInvoiceAmount,
                              IsActive = inv.IsActive,

                              CompanyID = cnt.MstCompanyID,
                              ClientID = cnt.MstClientID,
                              ContractID = cnt.MstContractID,
                              PaymentModeID = cnt.MstPaymentModeID,
                              TrnContractID = cnt.TrnContractID,
                              PaymentLevelID = inv.MstPaymentLevelID,
                              PaymentLevelName = payl.Name,

                              CompanyName = com.Name,
                              ContractName = c.Name,
                              ClientName = cli.ClientName,
                              PaymentModeName = pay.PaymentModeName,
                              ContractNo = cnt.ContractNo,

                              //DueDays = (DateTime.Today - inv.InvoiceDate),
                              DueAmount = inv.FinalInvoiceAmount - InvdAmt
                          });

            if (!string.IsNullOrEmpty(pObjInvoice.SeachClientIDs))
            {
                lstArr = pObjInvoice.SeachClientIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ClientID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchCompanyIDs))
            {
                lstArr = pObjInvoice.SearchCompanyIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.CompanyID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchContractIDs))
            {
                lstArr = pObjInvoice.SearchContractIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.ContractID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SearchPaymentModeIDs))
            {
                lstArr = pObjInvoice.SearchPaymentModeIDs.Split('~').Select(Int32.Parse).ToList();
                varRes = varRes.Where(s => lstArr.Contains(s.PaymentModeID ?? 0));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.SeachContractNo))
            {
                lstArrStr = pObjInvoice.SeachContractNo.Split('~').ToList();
                varRes = varRes.Where(s => lstArrStr.Contains(s.ContractNo));
            }
            if (!string.IsNullOrEmpty(pObjInvoice.InvoiceNo))
            {
                lstArrStr = pObjInvoice.InvoiceNo.Split('~').ToList();
                varRes = varRes.Where(s => lstArrStr.Contains(s.InvoiceNo));
            }
             
            if (pObjInvoice.SearchInvoiceStartDate != null)
                varRes = varRes.Where(s => s.InvoiceDate >= pObjInvoice.SearchInvoiceStartDate);
            if (pObjInvoice.SearchInvoiceEndDate != null)
                varRes = varRes.Where(s => s.InvoiceDate <= pObjInvoice.SearchInvoiceEndDate);             

            return varRes.ToList();
        }
    }
}